using Dapper;
using System;
using System.Collections.Generic;
using System.Data;

/**
*┌──────────────────────────────────────────────────────────────┐
*│　描    述：实物结存表接口实现
*│　作    者：李宝
*│　版    本：1.0 模板代码自动生成
*│　创建时间：2019-01-05 16:55:24
*└──────────────────────────────────────────────────────────────┘
*┌──────────────────────────────────────────────────────────────┐
*│　命名空间： Usido.CRM.Repository
*│　类    名： StockProductBalanceRepository
*└──────────────────────────────────────────────────────────────┘
*/

using Usido.CRM.Core.DbHelper;
using Usido.CRM.Core.Extensions;
using Usido.CRM.Core.Options;
using Usido.CRM.Core.Repository;
using Usido.CRM.Dto;
using Usido.CRM.IRepository;
using Usido.CRM.Models;

namespace Usido.CRM.Repository
{
    public class StockProductBalanceRepository : BaseRepository<StockProductBalance, int>, IStockProductBalanceRepository
    {
        public StockProductBalanceRepository()
        {
            _dbConnection = ConnectionFactory.CreateConnection(DbOptionData.DbType, DbOptionData.ConnectionString);
        }

        public decimal GetProductStock(int productId, DateTime dateExpiry, string batchNumber)
        {
            var stock = _dbConnection.ExecuteScalar<decimal>("P_GetProductStock", new { ProductId = productId, DateExpiry = dateExpiry, BatchNumber = batchNumber }, commandType: CommandType.StoredProcedure);

            return stock;
        }

        public List<decimal> GetReport(string begin, string end)
        {
            string sql = $@"
SELECT CONVERT(DECIMAL(18,2),ISNULL(SUM(A.Quantity * A.Price),0)) FROM dbo.SalesInfo AS A
INNER JOIN dbo.SalesOrder AS B ON A.OrderId = B.Id
WHERE B.State = 1
AND CONVERT(VARCHAR, B.CreateTime, 23) BETWEEN '{begin}' AND '{end}'
UNION ALL
SELECT CONVERT(DECIMAL(18,2),ISNULL(SUM(A.Quantity * A.Price),0)) FROM dbo.SalesReturnInfo AS A
INNER JOIN dbo.SalesReturnOrder AS B ON A.OrderId = B.Id
WHERE B.State = 1
AND CONVERT(VARCHAR, B.CreateTime, 23) BETWEEN '{begin}' AND '{end}'
UNION ALL
SELECT CONVERT(DECIMAL(18,2),ISNULL(SUM(A.Quantity * A.Price),0)) FROM dbo.SupplyInfo AS A
INNER JOIN dbo.SupplyOrder AS B ON A.OrderId = B.Id
WHERE B.State = 1
AND CONVERT(VARCHAR, B.CreateTime, 23) BETWEEN '{begin}' AND '{end}'
UNION ALL
SELECT CONVERT(DECIMAL(18,2),ISNULL(SUM(A.Quantity * A.Price),0)) FROM dbo.SupplyReturnInfo AS A
INNER JOIN dbo.SupplyReturnOrder AS B ON A.OrderId = B.Id
WHERE B.State = 1
AND CONVERT(VARCHAR, B.CreateTime, 23) BETWEEN '{begin}' AND '{end}'
UNION ALL
SELECT CONVERT(DECIMAL(18,2),ISNULL(SUM(TotalMoney - (T.Quantity * B.CostPrice)),0)) FROM (
	SELECT ProductId,BatchNumber,DateExpiry ,SUM(Quantity) Quantity,SUM(TotalMoney) AS TotalMoney FROM (
		SELECT ProductId,BatchNumber,DateExpiry ,Quantity,Price*Quantity AS TotalMoney
		FROM dbo.SalesInfo AS A
		INNER JOIN dbo.SalesOrder AS B ON A.OrderId = B.Id WHERE B.State = 1
			AND CONVERT(VARCHAR, B.CreateTime, 23) BETWEEN '{begin}' AND '{end}'
		UNION ALL
		SELECT ProductId,BatchNumber,DateExpiry ,-Quantity,-Price*Quantity AS TotalMoney
		FROM dbo.SalesReturnInfo AS A
		INNER JOIN dbo.SalesReturnOrder AS B ON A.OrderId = B.Id WHERE B.State = 1
			AND CONVERT(VARCHAR, B.CreateTime, 23) BETWEEN '{begin}' AND '{end}'
	)T
	GROUP BY ProductId,BatchNumber,DateExpiry
)T
INNER JOIN dbo.StockProductBalance AS B ON T.ProductId = B.ProductId AND B.BatchNumber = T.BatchNumber AND B.DateExpiry = T.DateExpiry
";
            return _dbConnection.Query<decimal>(sql).AsList();
        }

        public IEnumerable<StockProductBalanceDto> GetStockBalanceList(string where)
        {
            string sql = @"
SELECT * FROM (
	SELECT A.*,B.ProductName,B.ProductCode,B.Specification,B.ManufacturingEnterprise,
	B.MarketingUnit, A.CostPrice * A.StockQuantity AS TotalMoney FROM dbo.StockProductBalance AS A
	INNER JOIN dbo.SysProduct AS B ON A.ProductId = B.Id
)T";
            if (!where.IsNullOrEmpty())
            {
                sql += where;
            }
            return _dbConnection.Query<StockProductBalanceDto>(sql);
        }

        public IEnumerable<StockInfoDto> GetStockInfoList(string code)
        {
            string sql = $@"
SELECT A.Id,A.ProductId, B.ProductName,B.ProductCode,B.Specification,B.MarketingUnit,B.ManufacturingEnterprise,B.RetailPrice, B.MemberDiscount,
dbo.Fun_GetProductStock(A.Id) AS AvailableQuantity,A.DateExpiry,A.BatchNumber
FROM dbo.StockProductBalance AS A
INNER JOIN dbo.SysProduct AS B ON A.ProductId = B.Id
WHERE B.State = 1 AND (B.ProductCode LIKE '%{code}%' OR B.ProductName LIKE '%{code}%')
";

            return _dbConnection.Query<StockInfoDto>(sql);
        }

        public bool UpdateReservedStock(int id, decimal reservedStock)
        {
            string sql = @"UPDATE dbo.StockProductBalance SET ReservedStock = @ReservedStock WHERE Id = @Id";
            return _dbConnection.Execute(sql, new { ReservedStock = reservedStock, Id = id }) > 0;
        }
    }
}